﻿IF OBJECT_ID ( 'uspGetthreadpage', 'P' ) IS NOT NULL 
    DROP PROCEDURE uspGetthreadpage;
GO
CREATE PROCEDURE uspGetthreadpage 
	 @collegeid int,
	 @threadtype int,
	 @pagesize int,
	 @currentpage int,
	 @totalrecords int output
AS
    SET NOCOUNT ON;

declare @totalpages int	

select  @totalrecords=COUNT(*) from Forum where collegeid=@collegeid and (@threadtype<>0 or 1=1) and (@threadtype=0 or threadtype=@threadtype)
set @totalpages=@totalrecords/@pagesize
if(@totalrecords%@pagesize<>0)
	set @totalpages= @totalpages+1

if(@currentpage>@totalpages)
	set @currentpage=@totalpages
;

with vForum as (
	SELECT ROW_NUMBER() OVER (ORDER BY isontop desc,lastreplytime desc) AS RowNumber,
	threadid,Forum.collegeid,threadtype,typename,threadsubject,'' as [content], 
	createuserid,createusername,hidename,canreplyhidename,
	isapproved,checkuserid,checkusername,createtime,checktime,replycount,
	visitedcount,lastreplytime,lastreplyuserid,lastreplyusername,lastreplyhidename,
	isontop,canreply 
	FROM Forum
	left join ThreadType on Forum.collegeid=ThreadType.collegeid and Forum.threadtype=ThreadType.typeid
	where Forum.collegeid=@collegeid and (@threadtype<>0 or 1=1) and (@threadtype=0 or threadtype=@threadtype)
)



SELECT  * 
 FROM 
    vForum
 WHERE RowNumber > @pagesize*(@currentpage-1) and RowNumber <= @pagesize*@currentpage

GO

IF OBJECT_ID ( 'uspGetthreadreply', 'P' ) IS NOT NULL 
    DROP PROCEDURE uspGetthreadreply;
GO
CREATE PROCEDURE uspGetthreadreply 
	 @threadid int,
	 @pagesize int,
	 @currentpage int,
	 @totalrecords int output
AS
    SET NOCOUNT ON;

declare @totalpages int	
select  @totalrecords=COUNT(*) from ForumReply where threadid=@threadid
set @totalpages=@totalrecords/@pagesize

if(@totalrecords%@pagesize<>0)
	set @totalpages= @totalpages+1

if(@currentpage>@totalpages)
	set @currentpage=@totalpages
;
with vForumReply as (
	SELECT ROW_NUMBER() OVER (ORDER BY replytime asc) AS RowNumber,
	autoid,threadid,replyuserid,replyusername,content,replytime,hidename,isdeletedbyadmin
	FROM ForumReply
	where threadid=@threadid
)

SELECT  * 
 FROM 
    vForumReply
 WHERE RowNumber > @pagesize*(@currentpage-1) and RowNumber <= @pagesize*@currentpage


GO
